package com.example.musicplayer.util

import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import android.provider.BaseColumns
import android.util.Log

class MusicDatabaseHelper(context: Context) : SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION) {

    companion object {
        private const val DATABASE_NAME = "songs.db"
        private const val DATABASE_VERSION = 3

        private const val TABLE_SONGS = "songs"
        private const val COLUMN_ID = BaseColumns._ID
        private const val COLUMN_TITLE = "title"
        private const val COLUMN_ARTIST = "artist"
        private const val COLUMN_IMAGE = "image"
        private const val COLUMN_AUDIO_PATH = "audio_path"
        private const val COLUMN_CATEGORY_ID = "category_id"

        // SQL语句：创建歌曲表
        private const val SQL_CREATE_SONGS = """
            CREATE TABLE $TABLE_SONGS (
                $COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT,
                $COLUMN_TITLE TEXT NOT NULL,
                $COLUMN_ARTIST TEXT NOT NULL,
                $COLUMN_IMAGE BLOB,
                $COLUMN_AUDIO_PATH TEXT NOT NULL,
                $COLUMN_CATEGORY_ID INTEGER,
                FOREIGN KEY ($COLUMN_CATEGORY_ID) REFERENCES song_category(id)
            )
        """

        // SQL语句：删除歌曲表
        private const val SQL_DELETE_SONGS = "DROP TABLE IF EXISTS $TABLE_SONGS"
    }

    override fun onCreate(db: SQLiteDatabase) {
        // 确保 song_category 表已经存在
        val createCategoryTable = """
            CREATE TABLE IF NOT EXISTS song_category (
                ${BaseColumns._ID} INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL
            )
        """.trimIndent()
        db.execSQL(createCategoryTable)
        Log.d("MusicDatabaseHelper", "Table created: $createCategoryTable")

        db.execSQL(SQL_CREATE_SONGS)
        Log.d("MusicDatabaseHelper", "Table created: $SQL_CREATE_SONGS")
    }

    override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) {
        db.execSQL(SQL_DELETE_SONGS)
        Log.d("MusicDatabaseHelper", "Table dropped: $SQL_DELETE_SONGS")

        // 重新创建所有表
        onCreate(db)
    }
}